package com.aree.caipiao.shsq.dao.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.stereotype.Repository;

import com.aree.caipiao.shsq.clawer.ShsqClawer;

/**
 * jdbc操作数据库的基类
 * @author hp-pc
 *
 */
@Repository
public abstract class GenericDao<T> {
	protected Log log = LogFactory.getLog(ShsqClawer.class);
	
	private Properties properties;
	
	public GenericDao() {
		try {
			Resource resource = new ClassPathResource("db.properties");
			properties = new Properties();
			properties.load(resource.getInputStream());
		} catch (Exception e) {
			throw new IllegalStateException("初始化数据库连接信息失败", e);
		}
	}
	
	/**
	 * 查询单个对象
	 * @param querySql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	protected T query(String querySql, Object... params) throws SQLException {
		if (!querySql.contains("select")) throw new SQLException("查询sql需含select关键字");
		Connection connection = openConn();
	    PreparedStatement ps =connection.prepareStatement(querySql);
	    if (params != null) {
	    	for (int i = 0; i < params.length; i++) {
	    		ps.setObject(i+1, params[i]);
    		} 
	    }
	    ResultSet rs = ps.executeQuery();
	    if (rs == null || !rs.next()) return null;
	    T result = buildFromResultSet(rs);
	    rs.close();
	    ps.close();
	    connection.close();
		return result;
	}
	
	/**
	 * 查询对象列表
	 * @param querySql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	protected List<T> queryList(String querySql, Object... params) throws SQLException {
		if (!querySql.contains("select")) throw new SQLException("查询sql需含select关键字");
		Connection connection = openConn();
	    PreparedStatement ps =connection.prepareStatement(querySql);
	    if (params != null) {
	    	for (int i = 0; i < params.length; i++) {
	    		ps.setObject(i+1, params[i]);
    		} 
	    }
	    ResultSet rs = ps.executeQuery();
	    if (rs == null || !rs.next()) return null;
	    List<T> list = new ArrayList<T>();
	    while (rs.next()) {
	    	list.add(buildFromResultSet(rs));
	    }
	    rs.close();
	    ps.close();
	    connection.close();
		return list;
	}
	
	/**
	 * 将数据库查询结果拼装成对象
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	protected abstract T buildFromResultSet(ResultSet rs) throws SQLException;
	
	/**
	 * 打开数据库连接
	 * @return
	 */
	protected Connection openConn() {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			String dburl = properties.getProperty("datasource.url");
			String dbuser = properties.getProperty("datasource.username");
			String dbpwd = properties.getProperty("datasource.password");
		    // 提供地址用户名密码并获得连接对象
			return DriverManager.getConnection(dburl, dbuser, dbpwd);
		} catch (Exception e) {
			throw new IllegalStateException("打开数据库连接失败");
		}
	} 
	
	/**
	 * 执行无须返回对象的sql
	 * @param sql
	 * @param params
	 * @throws SQLException
	 */
	protected void execute(String sql, Object... params) throws SQLException {
		Connection connection = openConn();
	    PreparedStatement ps =connection.prepareStatement(sql);
	    if (params != null) {
	    	for (int i = 0; i < params.length; i++) {
	    		ps.setObject(i+1, params[i]);
    		} 
	    }
	    ps.executeUpdate();
	    ps.close();
	    connection.close();
	}
	

}
